<?php
// -----------------------------------------------------
// Created by : php-orm
// -----------------------------------------------------
// Date: 2021/5/27
// -----------------------------------------------------
// Time: 13:28
// -----------------------------------------------------
// User: tf <1096392101@qq.com>
// -----------------------------------------------------
declare (strict_types=1);

namespace Gao\orm\db;


abstract class Builder
{
    /**
     * 查询表达式映射
     * @var array
     */
    protected $exp = ['NOTLIKE' => 'NOT LIKE', 'NOTIN' => 'NOT IN', 'NOTBETWEEN' => 'NOT BETWEEN', 'NOTEXISTS' => 'NOT EXISTS', 'NOTNULL' => 'NOT NULL', 'NOTBETWEEN TIME' => 'NOT BETWEEN TIME'];

    /**
     * 查询表达式解析
     * @var array
     */
    protected $parser = [
        'parseCompare'     => ['=', '<>', '>', '>=', '<', '<='],
        'parseLike'        => ['LIKE', 'NOT LIKE'],
        'parseBetween'     => ['NOT BETWEEN', 'BETWEEN'],
        'parseIn'          => ['NOT IN', 'IN'],
        'parseExp'         => ['EXP'],
        'parseNull'        => ['NOT NULL', 'NULL'],
        'parseBetweenTime' => ['BETWEEN TIME', 'NOT BETWEEN TIME'],
        'parseTime'        => ['< TIME', '> TIME', '<= TIME', '>= TIME'],
        'parseExists'      => ['NOT EXISTS', 'EXISTS'],
        'parseColumn'      => ['COLUMN'],
    ];

    /**
     * SELECT SQL表达式
     * @var string $selectSql
     */
    protected $selectSql = 'SELECT%DISTINCT%%EXTRA% %FIELD% FROM %TABLE%%FORCE%%JOIN%%WHERE%%GROUP%%HAVING%%UNION%%ORDER%%LIMIT% %LOCK%%COMMENT%';

    /**
     * INSERT SQL表达式
     * @var string $insertSql
     */
    protected $insertSql = '%INSERT%%EXTRA% INTO %TABLE% (%FIELD%) VALUES (%DATA%) %COMMENT%';

    /**
     * INSERT ALL SQL表达式
     * @var string $insertAllSql
     */
    protected $insertAllSql = '%INSERT%%EXTRA% INTO %TABLE% (%FIELD%) %DATA% %COMMENT%';

    /**
     * UPDATE SQL表达式
     * @var string $updateSql
     */
    protected $updateSql = 'UPDATE%EXTRA% %TABLE% SET %SET%%JOIN%%WHERE%%ORDER%%LIMIT% %LOCK%%COMMENT%';

    /**
     * DELETE SQL表达式
     * @var string $deleteSql
     */
    protected $deleteSql = 'DELETE%EXTRA% FROM %TABLE%%USING%%JOIN%%WHERE%%ORDER%%LIMIT% %LOCK%%COMMENT%';

    /**
     * table分析
     * @access protected
     * @param Query $query     查询对象
     * @param  mixed     $tables    表名
     * @return string
     */
    protected function parseTable(Query $query, $tables): string
    {
        $options = $query->getOptions();

        return $options['prefix'] . $tables;
    }

    /**
     * distinct分析
     * @access protected
     * @param  mixed $distinct
     * @return string
     */
    protected function parseDistinct(bool $distinct): string
    {
        return !empty($distinct) ? ' DISTINCT ' : '';
    }

    /**
     * 查询额外参数分析
     * @access protected
     * @param  string $extra    额外参数
     * @return string
     */
    protected function parseExtra(Query $query,string $extra): string
    {
        return preg_match('/^[\w]+$/i', $extra) ? ' ' . strtoupper($extra) : '';
    }

    /**
     * 字段
     * @param $fields
     * @return string
     */
    protected function parseField($fields)
    {
        $fieldsStr = '';
        if (is_array($fields) && count($fields) > 0) {
            foreach ($fields as $v) {
                $fieldsStr .= "`{$v}`,";
            }
            $fieldsStr = rtrim($fieldsStr, ',');
        } else {
            $fieldsStr .= '*';
        }

        return $fieldsStr;
    }

    /**
     * where 条件
     * @param array $where
     * @return string
     */
    protected function parseWhere(Query $query,array $where)
    {
        if (empty($where)) {
            $where = [];
        }

        $whereStr = '';

        foreach ($where as $k => $v) {
            $whereStr .= $k.'='.$v. 'AND';
        }

        return $where ? ' WHERE '.rtrim($whereStr,'AND') : '';
    }

    /**
     * join分析
     * @access protected
     * @param  array $join
     * @return string
     */
    protected function parseJoin(Query $query,array $join): string
    {
        if (empty($join)) {
            return '';
        }
        $joinStr = '';

        foreach ($join as $item) {
            [$table, $type, $on] = $item;

            $table = $this->parseTable($query,$table);

            $joinStr .= ' ' . $type . ' JOIN ' . $table . ' ON ' . $on;
        }

        return $joinStr;
    }

    /**
     * group 分析
     * @param Query $query
     * @param $group
     * @return string
     */
    protected function parseGroup(Query $query,$group): string
    {
        if (empty($group)) {
            return '';
        }

        if (is_string($group)) {
            $group = explode(',', $group);
        }

        return ' GROUP BY ' . implode(',', $group);
    }

    /**
     * having分析
     * @param string $having
     * @return string
     */
    protected function parseHaving(Query $query,string $having): string
    {
        return !empty($having) ? ' HAVING ' . $having : '';
    }

    /**
     * order 处理
     * @param array $order
     * @return string
     */
    protected function parseOrder(Query $query,array $order): string
    {
        $array = [];
        foreach ($order as $key => $value) {
            if (preg_match('/^[\w\.]+$/', $key)) {
                $sort    = strtoupper($value);
                $sort    = in_array($sort, ['ASC', 'DESC'], true) ? ' ' . $sort : '';
                $array[] = $key . $sort;
            } else {
                throw new \Exception('order express error:' . $key);
            }
        }

        return empty($array) ? '' : ' ORDER BY '. implode(',',$array);
    }

    /**
     * limit 处理
     * @param string $limit
     * @return string
     */
    protected function parseLimit(Query $query,string $limit): string
    {
        return (!empty($limit) && false === strpos($limit, '(')) ? ' LIMIT ' . $limit . ' ' : '';
    }

    /**
     * 子查询
     * @param $union
     * @return string
     */
    protected function parseUnion($union): string
    {
        if (empty($union)) {
            return '';
        }

        $type = $union['type'];
        unset($union['type']);

        foreach ($union as $u) {
            $sql[] = $type . ' ( ' . $u . ' )';
        }

        return ' ' . implode(' ', $sql);
    }

    /**
     * 设置锁机制
     * @param false $lock
     * @return string
     */
    protected function parseLock(Query $query,$lock = false): string
    {
        if (is_bool($lock)) {
            return $lock ? ' FOR UPDATE ' : '';
        }

        if (is_string($lock) && !empty($lock)) {
            return ' ' . trim($lock) . ' ';
        } else {
            return '';
        }
    }

    /**
     * index分析，可在操作链中指定需要强制使用的索引
     * @access protected
     * @param  mixed $index
     * @return string
     */
    protected function parseForce($index): string
    {
        if (empty($index)) {
            return '';
        }

        if (is_array($index)) {
            $index = join(',', $index);
        }

        return sprintf(" FORCE INDEX ( %s ) ", $index);
    }

    /**
     * comment分析
     * @access protected
     * @param  Query  $query  查询对象
     * @param  string $comment
     * @return string
     */
    protected function parseComment(Query $query, string $comment): string
    {
        if (false !== strpos($comment, '*/')) {
            $comment = strstr($comment, '*/', true);
        }

        return !empty($comment) ? ' /* ' . $comment . ' */' : '';
    }


    /**
     * 生成查询SQL
     * @access public
     * @param Query $query 查询对象
     * @param bool $one 是否仅获取一个记录
     * @return string
     * @throws \Exception
     */
    public function select(Query $query, bool $one = false): string
    {
        $options = $query->getOptions();

        return str_replace(
            ['%TABLE%', '%DISTINCT%', '%EXTRA%', '%FIELD%', '%JOIN%', '%WHERE%', '%GROUP%', '%HAVING%', '%ORDER%', '%LIMIT%', '%UNION%', '%LOCK%', '%COMMENT%', '%FORCE%'],
            [
                $this->parseTable($query, $options['table']),
                $this->parseDistinct($options['distinct']),
                $this->parseExtra($query,$options['extra']),
                $this->parseField($options['field'] ?? '*'),
                $this->parseJoin($query,$options['join']),
                $this->parseWhere($query,$options['where']),
                $this->parseGroup($query,$options['group']),
                $this->parseHaving($query,$options['having']),
                $this->parseOrder($query,$options['order']),
                $this->parseLimit($query,$one ? '1' : $options['limit']),
                $this->parseUnion($options['union']),
                $this->parseLock($query,$options['lock']),
                $this->parseComment($query,$options['comment']),
                $this->parseForce($options['force']),
            ],
            $this->selectSql);
    }

    /**
     * 生成Insert SQL
     * @access public
     * @param  Query $query 查询对象
     * @return string
     */
    public function insert(Query $query): string
    {
        $options = $query->getOptions();

        // 分析并处理数据
        $data = $options['data'] ?? [];
        if (empty($data)) {
            return '';
        }

        $fields = array_keys($data);
        foreach ($fields as $key => $field) {
            $fields[$key] = '`'.$field.'`';
        }
        $values = array_values($data);
        foreach ($values as $key => $value) {
            $values[$key] = "'".$value."'";
        }

        return str_replace(
            ['%INSERT%', '%TABLE%', '%EXTRA%', '%FIELD%', '%DATA%', '%COMMENT%'],
            [
                !empty($options['replace']) ? 'REPLACE' : 'INSERT',
                $this->parseTable($query, $options['table']),
                $this->parseExtra($query, $options['extra']),
                implode(' , ', $fields),
                implode(' , ', $values),
                $this->parseComment($query, $options['comment']),
            ],
            $this->insertSql);
    }

    /**
     * 生成update SQL
     * @access public
     * @param Query $query 查询对象
     * @return string
     * @throws \Exception
     */
    public function update(Query $query): string
    {
        $options = $query->getOptions();

        $data = $options['data'] ?? '';

        if (empty($data)) {
            return '';
        }

        $set = [];
        foreach ($data as $key => $val) {
            $set[] = '`' . $key . '`' . ' = ' . "'" . $val . "'";
        }

        return str_replace(
            ['%TABLE%', '%EXTRA%', '%SET%', '%JOIN%', '%WHERE%', '%ORDER%', '%LIMIT%', '%LOCK%', '%COMMENT%'],
            [
                $this->parseTable($query, $options['table']),
                $this->parseExtra($query, $options['extra']),
                implode(' , ', $set),
                $this->parseJoin($query, $options['join']),
                $this->parseWhere($query, $options['where']),
                $this->parseOrder($query, $options['order']),
                $this->parseLimit($query, $options['limit']),
                $this->parseLock($query, $options['lock']),
                $this->parseComment($query, $options['comment']),
            ],
            $this->updateSql);
    }

    /**
     * 生成delete SQL
     * @access public
     * @param Query $query 查询对象
     * @return string
     * @throws \Exception
     */
    public function delete(Query $query): string
    {
        $options = $query->getOptions();

        return str_replace(
            ['%TABLE%', '%EXTRA%', '%USING%', '%JOIN%', '%WHERE%', '%ORDER%', '%LIMIT%', '%LOCK%', '%COMMENT%'],
            [
                $this->parseTable($query, $options['table']),
                $this->parseExtra($query, $options['extra']),
                !empty($options['using']) ? ' USING ' . $this->parseTable($query, $options['using']) . ' ' : '',
                $this->parseJoin($query, $options['join']),
                $this->parseWhere($query, $options['where']),
                $this->parseOrder($query, $options['order']),
                $this->parseLimit($query, $options['limit']),
                $this->parseLock($query, $options['lock']),
                $this->parseComment($query, $options['comment']),
            ],
            $this->deleteSql);
    }


}